﻿using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace TempCtr.Model
{
    internal class MySqlHelp
    {
        public static string GetDefName(long ID)
        {
            try
            {
                string sql = "SELECT defunct_name FROM funeral_logistics_antiseptic,thermostat where funeral_logistics_antiseptic.freezer_id=thermostat.def_id and thermostat.id=@thermostat_id and funeral_logistics_antiseptic.out_time <=> null";
                DataTable dataTable = MySqlBase.GetDataTabel(sql, new MySqlParameter("@thermostat_id", ID));
               
                if (dataTable.Rows.Count == 0) return "";
                else  return dataTable.Rows[0][0].ToString();
                
            }
            catch (MySqlException ex)
            {
                Log.Write(Log_Type.Error, ex.ToString());
                return "";
            }
        }

        public static string GetDefNum(long ID)
        {
            try
            {
                string sql = "SELECT defunct_number FROM funeral_logistics_antiseptic,thermostat where funeral_logistics_antiseptic.freezer_id=thermostat.def_id and thermostat.id=@thermostat_id and funeral_logistics_antiseptic.out_time <=> null";
                DataTable dataTable = MySqlBase.GetDataTabel(sql, new MySqlParameter("@thermostat_id", ID));
                if (dataTable.Rows.Count == 0) return "";
                else return dataTable.Rows[0][0].ToString();

            }
            catch (MySqlException ex)
            {
                Log.Write(Log_Type.Error, ex.ToString());
                return "";
            }
        }

        public static string GetDefAge(long ID)
        {
            try
            {
                string sql = "select defunct_age from funeral_defunct a join funeral_logistics_antiseptic b on a.defunct_number = b.defunct_number join thermostat c on c.def_id = b.freezer_id WHERE c.id=@thermostat_id  and b.out_time <=> null";
                DataTable dataTable = MySqlBase.GetDataTabel(sql, new MySqlParameter("@thermostat_id", ID));
                if (dataTable.Rows.Count == 0) return "";
                else return dataTable.Rows[0][0].ToString();

            }
            catch (MySqlException ex)
            {
                Log.Write(Log_Type.Error, ex.ToString());
                return "";
            }
        }

        public static string GetDefPic(long ID)
        {
            try
            {
                string sql = "select defunct_photo from funeral_defunct a join funeral_logistics_antiseptic b on a.defunct_number = b.defunct_number join thermostat c on c.def_id = b.freezer_id WHERE c.id=@thermostat_id  and b.out_time <=> null";
                DataTable dataTable = MySqlBase.GetDataTabel(sql, new MySqlParameter("@thermostat_id", ID));
                if (dataTable.Rows.Count == 0) return "";
                else return dataTable.Rows[0][0].ToString();

            }
            catch (MySqlException ex)
            {
                Log.Write(Log_Type.Error, ex.ToString());
                return "";
            }
        }

        public static string GetDefTime(long ID)
        {
            try
            {
                string sql = "SELECT entry_time FROM funeral_logistics_antiseptic,thermostat where funeral_logistics_antiseptic.freezer_id=thermostat.def_id and thermostat.id=@thermostat_id and funeral_logistics_antiseptic.out_time <=> null";
                DataTable dataTable = MySqlBase.GetDataTabel(sql, new MySqlParameter("@thermostat_id", ID));
                if (dataTable.Rows.Count == 0) return "";
                else return dataTable.Rows[0][0].ToString();

            }
            catch (MySqlException ex)
            {
                Log.Write(Log_Type.Error, ex.ToString());
                return "";
            }
        }

        public static int ADDThermostat(long  ID,string COM,long ADDR,long XNUM)
        {
            try
            {
                string sql = "INSERT INTO `thermostat`(id,num,com,addr,state,xnum) " +
                    "VALUES (@thermostat_id,@thermostat_num,@thermostat_com,@thermostat_addr,1,@thermostat_xnum);";
                return MySqlBase.ExecuteSQL(sql,new MySqlParameter("@thermostat_id", ID),
                    new MySqlParameter("@thermostat_num",ID),
                    new MySqlParameter("@thermostat_com", COM),
                    new MySqlParameter("@thermostat_addr", ADDR),
                    new MySqlParameter("@thermostat_xnum", XNUM)
                   );
            }
            catch (MySqlException ex)
            {
                Log.Write(Log_Type.Error, ex.ToString());
                return 0;
            }
        }
        public static int DELETEThermostat(long ID)
        {
            try
            {
                string sql = "DELETE FROM `thermostat` WHERE `id` = @thermostat_id;";
                return MySqlBase.ExecuteSQL(sql, new MySqlParameter("@thermostat_id", ID));
            }
            catch (MySqlException ex)
            {
                Log.Write(Log_Type.Error, ex.ToString());
                return 0;
            }
        }

        public static int UPDATEThermostatNum(long ID,string NUM)
        {
            try
            {
                string sql = "UPDATE thermostat SET num=@thermostat_num WHERE  id=@thermostat_id";
                return MySqlBase.ExecuteSQL(sql, new MySqlParameter("@thermostat_id", ID),
                    new MySqlParameter("@thermostat_num", NUM)          
                    );
            }
            catch (MySqlException ex)
            {
                Log.Write(Log_Type.Error, ex.ToString());
                return 0;
            }
        }

        public static int UPDATEThermostatCom(long XNUM, string COM)
        {
            try
            {
                string sql = "UPDATE thermostat SET com=@thermostat_com WHERE  xnum=@thermostat_xnum";
                return MySqlBase.ExecuteSQL(sql, new MySqlParameter("@thermostat_xnum", XNUM),
                    new MySqlParameter("@thermostat_com", COM)
                    );
            }
            catch (MySqlException ex)
            {
                Log.Write(Log_Type.Error, ex.ToString());
                return 0;
            }
        }

        public static int SWITCHhermostat(long ID,string SWITCH)
        {
            try
            {
                string sql = "UPDATE thermostat SET state=@thermostat_state  WHERE  id=@thermostat_id";
                return MySqlBase.ExecuteSQL(sql, new MySqlParameter("@thermostat_id", ID),
                    new MySqlParameter("@thermostat_state",SWITCH)
                    );
            }
            catch (MySqlException ex)
            {
                Log.Write(Log_Type.Error, ex.ToString());
                return 0;
            }
        }

        public static DataTable GetThermostat()
        {
            try
            {
                string sql = "SELECT * FROM `thermostat`;";
                return MySqlBase.GetDataTabel(sql);
            }
            catch (MySqlException ex)
            {
                Log.Write(Log_Type.Error, ex.ToString());
                return new DataTable();
            } 
        }
        public static DataTable GetThermostat(long XNUM)
        {
            try
            {
                string sql = "SELECT * FROM `thermostat` WHERE xnum=@thermostat_XNUM;";
                return MySqlBase.GetDataTabel(sql,new MySqlParameter("@thermostat_XNUM", XNUM));
            }
            catch (MySqlException ex)
            {
                Log.Write(Log_Type.Error, ex.ToString());
                return new DataTable();
            }
        }


        public static int AddTemp(long ID,double Temp,int STATE)
        {
            try
            {
                string sql = "INSERT INTO `thermostat_history`(`id`, `temp`, `state`,`time`) " +
                    "VALUES (@thermostat_id, @temp,@state, NOW());";
                return MySqlBase.ExecuteSQL(sql, 
                    new MySqlParameter("@thermostat_id", ID),
                    new MySqlParameter("@state", STATE),
                    new MySqlParameter("@temp", Temp));
            }
            catch (MySqlException ex)
            {
                Log.Write(Log_Type.Error, ex.ToString());
                return 0;
            }
        }
        public static DataTable GetTemp(long ID, DateTime StartTime,DateTime EndTime)
        {
            try
            {
                string sql = "SELECT * FROM `thermostat_history` " +
                    "WHERE id = @thermostat_id AND time BETWEEN @StartTime AND @EndTime;";
                return MySqlBase.GetDataTabel(sql,
                    new MySqlParameter("@thermostat_id", ID),
                    new MySqlParameter("@StartTime", StartTime),
                    new MySqlParameter("@EndTime", EndTime));
            }
            catch (MySqlException ex)
            {
                Log.Write(Log_Type.Error, ex.ToString());
                return new DataTable();
            }
        }

        public static DataTable GetTemp(long ID, DateTime StartTime, DateTime EndTime,int STATE)
        {
            try
            {
                string sql;
                if (ID == 0)
                {
                    sql = "SELECT id as `ID`,temp as `温度`,time as `时间`,case state when 0 then '温度正常' when 1 then '温度超高' when 2 then '温度过低' end as `状态`  FROM `thermostat_history` " +
                    "WHERE state=@thermostat_state AND time BETWEEN @StartTime AND @EndTime;";
                    return MySqlBase.GetDataTabel(sql,
                    new MySqlParameter("@StartTime", StartTime),
                    new MySqlParameter("@EndTime", EndTime),
                    new MySqlParameter("@thermostat_state", STATE));
                }
                else
                {
                    sql = "SELECT id as `ID`,temp as `温度`,time as `时间`,case state when 0 then '温度正常' when 1 then '温度超高' when 2 then '温度过低' end as `状态`  FROM `thermostat_history` " +
                    "WHERE id=@thermostat_id AND state=@thermostat_state AND time BETWEEN @StartTime AND @EndTime;";
                    return MySqlBase.GetDataTabel(sql,
                         new MySqlParameter("@thermostat_id", ID),
                    new MySqlParameter("@StartTime", StartTime),
                    new MySqlParameter("@EndTime", EndTime),
                    new MySqlParameter("@thermostat_state", STATE));
                }
        
            }
            catch (MySqlException ex)
            {
                Log.Write(Log_Type.Error, ex.ToString());
                return new DataTable();
            }
        }


        public static int AddLog(string module, string operate, string Event)
        {
            try
            {
                string sql = "INSERT INTO `control_thermostat_log`(`time`, `module`, `operate`, `event`) " +
                    "VALUES (NOW(), @module, @operate, @event);";
                return MySqlBase.ExecuteSQL(sql, 
                    new MySqlParameter("@module", module),
                    new MySqlParameter("@operate", operate),
                    new MySqlParameter("@event", Event));
            }
            catch (MySqlException ex)
            {
                Log.Write(Log_Type.Error, ex.ToString());
                return 0;
            }
        }
        public static DataTable GetLog(DateTime StartTime, DateTime EndTime)
        {
            try
            {
                string sql = "SELECT log_id as '编号',time as '时间',module as '模块',operate as '操作',event as '事件'" +
                    " FROM `control_thermostat_log` " +
                    "WHERE time BETWEEN @StartTime AND @EndTime;";
                return MySqlBase.GetDataTabel(sql,
                    new MySqlParameter("@StartTime", StartTime),
                    new MySqlParameter("@EndTime", EndTime));
            }
            catch (MySqlException ex)
            {
                Log.Write(Log_Type.Error, ex.ToString());
                return new DataTable();
            }
        }
    }
}
